package com.dao;

import com.bean.Book;
import com.utils.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    /**
     * 添加方法
     *
     * @param name
     * @param author
     * @param price
     * @param press
     * @param sales
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void insert(String name, String author, float price, String press, String sales) throws SQLException, ClassNotFoundException {
        String sql = "insert into book (book_name, book_author, book_price, book_press, book_sales) values (?, ?, ?, ?, ?)";
        System.out.println("要执行的语句是：" + sql);

        // 创建连接对象
        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, name);
            statement.setString(2, author);
            statement.setFloat(3, price);
            statement.setString(4, press);
            statement.setString(5, sales);
            statement.executeUpdate();
        }
    }

    /**
     * 删除方法
     *
     * @param id
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void delete(int id) throws SQLException, ClassNotFoundException {
        String sql = "delete from book where book_id = ?";
        System.out.println("要执行的语句是：" + sql);

        // 创建连接对象
        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, id);

            int rows = statement.executeUpdate();
            System.out.println("要删除的行数：" + rows);
        }
    }

    /**
     * 查询所有信息方法
     * 查询方法一般用数组接收：List<Book>
     *
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Book> list() throws SQLException, ClassNotFoundException {
        List<Book> books = new ArrayList<>();
        String sql = "select book_id, book_name, book_author, book_price, book_press, book_sales from book";
        System.out.println("要执行的语句是：" + sql);

        // 创建对象
        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            // 这里查询出来是多条数据，所以用 while 循环
            while (resultSet.next()) {
                books.add(
                    new Book(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getString(5),
                        resultSet.getString(6)
                    )
                );
            }
        }
        return books;
    }
/*            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String author = resultSet.getString(3);
                float price = resultSet.getFloat(4);
                String press = resultSet.getString(5);
                String sales = resultSet.getString(6);

                Book book = new Book(id, name, author, price, press, sales);
                books.add(book);
            }
            return books;
        }*/

    /**
     * 模糊查询方法（参数：condition）
     * 可以根据图书名称和图书产地查询
     * 模糊查询也用数组接收：List<Book>
     *
     * @param condition
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Book> list(String condition) throws SQLException, ClassNotFoundException {
        List<Book> books = new ArrayList<>();
        String sql = "select book_id, book_name, book_author, book_price, book_press, book_sales from book where book_name like ? or book_press like ?";
        System.out.println("要执行的语句是：" + sql);

        // 创建连接对象
        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, "%" + condition + "%");
            statement.setString(2, "%" + condition + "%");

            try (ResultSet resultSet = statement.executeQuery()) {
                // 这里查询出来是多条数据，所以用 while 循环
                while (resultSet.next()) {
                    int id = resultSet.getInt(1);
                    String name = resultSet.getString(2);
                    String author = resultSet.getString(3);
                    float price = resultSet.getFloat(4);
                    String press = resultSet.getString(5);
                    String sales = resultSet.getString(6);

                    // 创建对象，有简洁写法，可以参照查询所有信息方法
                    Book book = new Book();
                    book.setId(id);
                    book.setName(name);
                    book.setAuthor(author);
                    book.setPrice(price);
                    book.setPress(press);
                    book.setSales(sales);
                    books.add(book);
                }
                return books;
            }
        }
    }

    /**
     * 修改方法前要先查询单条记录
     *
     * @param id
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public Book get(int id) throws SQLException, ClassNotFoundException {
        String sql = "select book_id, book_name, book_author, book_price, book_press, book_sales from book where book_id = ?";
        System.out.println("要执行的语句是：" + sql);

        // 创建连接对象
        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, id);

            try (ResultSet resultSet = statement.executeQuery()) {
                resultSet.next();
                return new Book(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getString(5),
                        resultSet.getString(6));
            }
        }
    }

    /**
     * 修改方法
     * 重点：先查询再修改
     *
     * @param id
     * @param name
     * @param author
     * @param price
     * @param press
     * @param sales
     * @throws Exception
     * @throws ClassNotFoundException
     */
    public void update(int id, String name, String author, float price, String press, String sales) throws Exception, ClassNotFoundException {
        String sql = "update book set book_name = ?, book_author = ?, book_price = ?, book_press = ?, book_sales = ? where book_id = ?";
        System.out.println("要执行的语句是：" + sql);

        // 创建连接对象
        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, name);
            statement.setString(2, author);
            statement.setFloat(3, price);
            statement.setString(4, press);
            statement.setString(5, sales);
            statement.setInt(6, id);

            int rows = statement.executeUpdate();
            if (rows == 0) {
                throw new Exception("没有执行任何更新语句！");
            }
        }
    }
}
